/**
 * 
 */
package com.school.dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.logging.Logger;

import com.school.dao.ConnectionUtil;
import com.school.entity.Login;
import com.school.entity.NewRegistration;
/**
 * @author KK
 *
 */
public class UserManagerDaoImpl implements UserManagerDao{
	
	Logger log = Logger.getLogger(UserManagerDaoImpl.class.getSimpleName());
	@Override
	public boolean checkExist(String id) {
		// TODO Auto-generated method stub
		return false;
	}
	
	@Override
	public boolean verifyUser(Login login) {
		log.info("Entered verifyUser()");
		log.info("Entered into booleean user verification-------kkkkkkkk ");

		Connection con = null;
		ResultSet rs = null;
		boolean validUser = false;
		try {
			log.info("TRying for get connection-------kkkkkkkk ");
			con = ConnectionUtil.getConnection();

			String query = "SELECT * from login where id=?";
			log.info("Select star from login-------kkkkkkkk ");			
			
			PreparedStatement statement = con.prepareStatement(query);
			statement.setString(1, login.getLoginId());
			log.info("Query prepared-------kkkkkkkk ");
			rs = statement.executeQuery();
			
			log.info("Executing Quiry-------kkkkkkkk ");
			log.info("extracting details,------- login type ===" + login.getType());
			int i=1;
			while(rs.next())
			{
				System.out.println("Matching Login input with row "+i+" of login table");
				if(rs.getString(1).equals(login.getLoginId()) && rs.getString(2).equals(login.getPassword()) && rs.getString(3).equals(login.getType()))
				{	validUser = true;
//					log.info("Match found for valid user after"+" "+i+" "+"trials");
					System.out.println("Match found for valid user after"+" "+i+" "+"trials");
					break;
				}
				i++;
			}
			rs = null;
//			log.info("User is valid? " + validUser);
			System.out.println("User is valid? " + validUser);
		}
		catch(Exception e) {
			validUser = false;
			e.printStackTrace();
		}
		finally {
			ConnectionUtil.CloseDbResources( con);
		}
		System.out.println("Exited verifyUser()===" + validUser);
		return validUser;
	}

	@Override
	public String new_register(NewRegistration newreg) {
		// TODO Auto-generated method stub
		
		System.out.println("Entered new_register method");
		Connection c = null;
		ResultSet rs = null;
		String ref2 = null;
	//	boolean regstatus = false;
		System.out.println("Before starting of try-----------ttttrrryyyy");
		try {
			System.out.println("Inside try-------iiinnnnnn");
			c = ConnectionUtil.getConnection();
			
			String q1 = "select max(S_NO) from new_registration";
			PreparedStatement s1 = c.prepareStatement(q1);
			rs = s1.executeQuery();
	//		System.out.println("After rs = execute query-------------rrsss");
			
			String ref1="null";
			while (rs.next()) {
				ref1 = rs.getString(1);
			}
			int i = Integer.parseInt(ref1);
			i++;
			
			ref2 = "SM#REF00"+i;
			String na = "NA";
	//		System.out.println("Generating reference number ---------"+ref2);
			//getting System current date
			java.util.Date javaDateObject = new java.util.Date();
			// Convert it to java.sql.Date
			Date today = new Date(javaDateObject.getTime());
			System.out.println(today);
			 
		 	String q2 = "INSERT INTO new_registration (REFERENCE_NO, REG_DATE, STUDENT_NAME, FATHER_NAME, DOB, ADDRESS, CITY, CONTACT_NO, EMAIL, PREVIOUS_EDUCATION, NEW_CLASS, EXAM_DATE, EXAM_STATUS, INTERVIEW_DATE, INTERVIEW_STATUS) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement s2 = c.prepareStatement(q2);
			
			s2.setString(1, ref2);
			s2.setDate(2, today);
			s2.setString(3, newreg.getStudent_name());
			s2.setString(4, newreg.getFather_name());
			s2.setDate(5, newreg.getDob());
			s2.setString(6, newreg.getAddress());
			s2.setString(7, newreg.getCity());
			s2.setString(8, newreg.getContact_no());
			s2.setString(9, newreg.getEmail());
			s2.setString(10, newreg.getPrev_edu());
			s2.setString(11, newreg.getNew_class());
			s2.setString(12, na);
			s2.setString(13, na);
			s2.setString(14, na);
			s2.setString(15, na);
			
			
			System.out.println("After setting insert hint for q2------kk");
			
			if(s2.executeUpdate()>0)
			{//	regstatus = true; 			
			System.out.println("Values inserted successfully into new_registration table----kkkk");
			}
		} 
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally {
			ConnectionUtil.CloseDbResources(c);
		}
		return ref2;
	}

	@Override
	public NewRegistration view_status(String ref) {
		// TODO Auto-generated method stub
		Connection c = null;
		ResultSet rs = null;
		NewRegistration status = new NewRegistration();
		try {
			c = ConnectionUtil.getConnection();
			String q1 = "SELECT REFERENCE_NO, STUDENT_NAME, EXAM_DATE, EXAM_STATUS, INTERVIEW_DATE, INTERVIEW_STATUS FROM new_registration WHERE REFERENCE_NO=?";
			PreparedStatement s1 = c.prepareStatement(q1);
			s1.setString(1, ref);
			rs = s1.executeQuery();
			while(rs.next()){
				
				status.setRef_no(rs.getString(1));
				System.out.println("ref no-------------"+rs.getString(1));			//
				status.setStudent_name(rs.getString(2));
				System.out.println("stud name--------------"+rs.getString(2));			//
				status.setExam_date(rs.getString(3));
				System.out.println("exam date--------------"+rs.getString(3));			//
				status.setExam_status(rs.getString(4));
				System.out.println("exam status---------------"+rs.getString(4));			//
				status.setInterview_date(rs.getString(5));
				System.out.println("int date--------------"+rs.getString(5));			//
				status.setInterview_status(rs.getString(6));
				System.out.println("int status------------------"+rs.getString(6));			//
			}
		} catch (Exception e) {
				e.printStackTrace();
		}
		finally {
			ConnectionUtil.CloseDbResources(c);
		}
		return status;
	}

	
}
